package com.hnas.hnt.dao;

import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Query;

import org.apache.commons.collections.CollectionUtils;
import org.springframework.stereotype.Component;

import com.hnas.common.domain.CommonSQL;
import com.hnas.core.dao.HntBaseDaoImpl;
import com.hnas.core.util.DateUtil;
import com.hnas.core.util.StringUtil;
import com.hnas.hnt.orm.EQPInfo;
import com.hnas.hnt.orm.Mrch;
import com.hnas.trade.domain.OpTranType;
import com.hnas.trade.service.dto.DTOResultTransApp;
import com.hnas.trade.service.dto.DTOSearchCBxfmx;
import com.hnas.trade.service.dto.DTOSearchTrans;
/**
 * 版权所有：2016-LBDOC 项目名称：LBDOC_DATAMANAGE
 * 类描述： 类名称：datamanage.libo.dao.DataTableDAOImpl 创建人：李博 创建时间：2016年7月18日
 * 下午1:43:41
 * 
 * @version1.0
 */
@Component("mrchDAO")
public class MrchDAOImpl extends HntBaseDaoImpl<Mrch> implements MrchDAO {
	
	public MrchDAOImpl() {
		super(Mrch.class);
	}
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	NumberFormat df = new DecimalFormat("#0.00");
	@Override
	public List<Mrch> getMrchs() {
		// TODO Auto-generated method stub
		
				String sql = "select MRCH_ID,INDUSTRY_ID,MRCH_NAME from m_mrch_basic_info t where  ";
				StringBuffer sb = new StringBuffer();
				List<Object> paras = new ArrayList<Object>();
				int j = 0;
				
				sb.append(" and MRCH_TYPE = ?" + j++);
				paras.add("2");
				
				if (sb.length() > 0) {
					sb.delete(0, 5);
					sb.insert(0, sql);
				} else {
					String substr=sql.substring(0,sql.indexOf("where"));
					sb.append(substr);
				}
				Query ps =this.entityManager.createNativeQuery(sb.toString());
				if(paras.size() > 0){
					for(int i=0; i<paras.size(); i++){
						ps.setParameter(i, paras.get(i));
					}
				}
				List<Object[]> ls = ps.getResultList();
				List<Mrch> rs = new ArrayList<Mrch>();
				for(Object[] obs:ls){
					Mrch re = new Mrch();
					re.setMRCH_NAME(obs[2].toString());
					rs.add(re);
				}
				return rs;
	}
	
	@Override
	public List<EQPInfo> getEQPsByMrchId(String mrchId){

		String sql = "select EQP.EQP_ID,EQP.EQP_NO from M_MRCH_EQP_INFO EQP LEFT JOIN M_MRCH_EQP_RELATION M ON EQP.EQP_ID=M.EQP_ID WHERE 1=1  and M.BIND_TIME>to_date('2016-10-01','yyyy-mm-dd') ";
		 sql =sql+ " and M.MRCH_ID in (" + StringUtil.getChangeString(mrchId)+")";
		Query ps =this.entityManager.createNativeQuery(sql);
		List<Object[]> ls = ps.getResultList();
		List<EQPInfo> rs = new ArrayList<EQPInfo>();
		for(Object[] obs:ls){
			EQPInfo re = new EQPInfo();
			re.setEQP_ID(obs[0].toString());
			re.setEQP_NO(obs[1].toString());
			rs.add(re);
		}
		return rs;
	}
	@Override
	public List<Mrch> getMrchsAll() {
		List<Mrch> list = this.findByQuery("from Mrch t where t.MRCH_TYPE= ?1", "2");
		return list;
	}
	@Override
	public List<Mrch> getMrchsByIndustryId(String industryId) {
		List<Mrch> list = this.findByQuery("from Mrch t where t.MRCH_TYPE= 2 and t.INDUSTRY_ID in ("+StringUtil.getChangeString(industryId)+")");
		return list;
	}
	@Override
	public List<Mrch> getMrchsById(String mrchID) {
		List<Mrch> list = this.findByQuery("from Mrch t where t.MRCH_ID in ("+StringUtil.getChangeString(mrchID)+")");
		return list;
	}

	
	@Override
	public String checkData(String id,String param) {
		String codeNo="00000";
		if(param.toLowerCase().equals("false")){
			Query ps =this.entityManager.createNativeQuery(id);
			List<Object> ls = ps.getResultList();
			if (CollectionUtils.isNotEmpty(ls)) {
			   codeNo=ls.get(0).toString();
			}
		}else{
			Query ps1 =this.entityManager.createNativeQuery(id);
			ps1.executeUpdate();
		}
		return codeNo;
	}

	@Override
	public List<DTOResultTransApp> searchTransAppNew(DTOSearchTrans dtoSearchTrans) {
		DTOSearchCBxfmx con=new DTOSearchCBxfmx();
		con.setCard_id(dtoSearchTrans.getCardNo());//卡号
		con.setStartDate(DateUtil.dateToString(DateUtil.stringToDate1(dtoSearchTrans.getStartDate())));//开始时间
		con.setEndDate(DateUtil.dateToString(DateUtil.stringToDate1(dtoSearchTrans.getEndDate())));//结束时间
		con.setUserRole(dtoSearchTrans.getSubCode());//科目号
		con.setTran_type_id(dtoSearchTrans.getTranType());//交易类型
		con.setCorrect(dtoSearchTrans.getTranState());//交易状态
//		int maxRows=Integer.valueOf(dtoSearchTrans.getStartPage())+Integer.valueOf(dtoSearchTrans.getEndPage());
		List<Object> paras = new ArrayList<Object>();
		String sh_cbxfmx_t_All="";
		//查所有的数据
		sh_cbxfmx_t_All=createAppNewSQL(con,paras,0,false,false);
		if(StringUtil.isNotEmpty(sh_cbxfmx_t_All)){
			sh_cbxfmx_t_All=sh_cbxfmx_t_All.substring(6);
        }
		//分页
		String sql = "select v.* from ("+sh_cbxfmx_t_All+") v order by to_date(TRAN_DATE,'YYYY-MM-DD HH24:MI:SS') desc";
		 sql = "select v.*,rownum as numRows  from ("+sql+") v ";
	     sql = "select * from ("+sql+")";
	    // System.out.println("同步sql:"+sql);
//		sql=sql+" where numRows>="+ dtoSearchTrans.getStartPage()+" and numRows<"+maxRows;
		//sql=sql+" order by to_date(TRAN_DATE,'YYYY-MM-DD HH24:MI:SS') desc";
		//分页查询列表
		System.out.println("查询语句:"+sql);
		Query ps =this.entityManager.createNativeQuery(sql);
		if(paras.size() > 0){
			for(int i=0; i<paras.size(); i++){
				ps.setParameter(i, paras.get(i));
				//System.out.println("查询参数"+i+"值:"+paras.get(i));
				
			}
		}
		List<Object[]> ls = ps.getResultList();
		//System.out.println("查询结果1:"+ls.size());
		List<DTOResultTransApp>  rs = new ArrayList<DTOResultTransApp>();
		for(Object[] obs:ls){
			DTOResultTransApp re = new DTOResultTransApp();
			if(null!=obs[0]){
				re.setTRAN_SNO(obs[0].toString());
			}
			if(null!=obs[1]){
				String obsStr =obs[1].toString();
				double d =Double.valueOf(obsStr)*100;
				String tran_value =  this.df.format(d);
				re.setTRAN_VALUE(tran_value.replace("￥", "").replace("$", ""));	
			}
			if(null!=obs[2]){
				String obsStr =obs[2].toString();
				double d =Double.valueOf(obsStr)*100;
				String tran_amt = this.df.format(d);
				re.setTRAN_AMT(tran_amt.replace("￥", "").replace("$", ""));	
			}
			if(null!=obs[3]){
				re.setTRAN_TYPE(obs[3].toString());
			}
			if(null!=obs[5]){
				re.setTRAN_STATE(obs[5].toString());
			}
			if(null!=obs[6]){
				re.setCARD_NO(obs[6].toString());
			}
			if(null!=obs[10]){
				re.setTRAN_MRCH(obs[10].toString());
			}
			if(null!=obs[11]){
				re.setMRCH_ID(obs[11].toString());
			}
			if(null!=obs[12]){
				re.setTRAN_DATE(DateUtil.dateToString1(DateUtil.stringToDate(obs[12].toString())));
				re.setTRAN_TIME(DateUtil.dateTimeToString1(DateUtil.stringToDate(obs[12].toString())));
			}
			if(null!=obs[14]){
				re.setEQP_ID(obs[14].toString());
			}
			if(null!=obs[15]){
				re.setPAYMENT(obs[15].toString());
			}
			if(null!=obs[16]){
				re.setCARD_BALANCE(obs[16].toString());
			}
			// 新增三充字段
			if(null!=obs[21]){
				re.setORDER_STATUS(obs[21].toString());
			}
			if(null!=obs[22]){
				re.setMOBILE_PHONE(obs[22].toString());
				if(StringUtil.isNotEmpty(obs[22].toString().trim())){
					re.setIS_OP_TRAN("1"); // 属于三充交易记录
				}else{
					re.setIS_OP_TRAN("0"); // 不属于三充交易记录
				}
			}
			if(null!=obs[23]){
				re.setOP_TELECOM_TYPE(OpTranType.getOpTelecomTypeStr(obs[23].toString()));
			}
			if(null!=obs[24]){
				re.setOP_PRODUCT_ID(obs[24].toString());
			}
			if(null!=obs[25]){
				re.setOP_PRODUCT_TYPE(OpTranType.getOpProductTypeStr(obs[25].toString()));
			}
			if(null!=obs[27]){
				re.setTRAN_SUB_TYPE(obs[27].toString());
			}
			if(null!=obs[28]){
				re.setRECH_CHANNEL(obs[28].toString());
			}
			rs.add(re);
		}
		return rs;
	}
	
	@Override
	public List<DTOResultTransApp> searchTransAppNew3(DTOSearchTrans dtoSearchTrans) {
		DTOSearchCBxfmx con=new DTOSearchCBxfmx();
		con.setCard_id(dtoSearchTrans.getCardNo());//卡号
		con.setStartDate(DateUtil.dateToString(DateUtil.stringToDate1(dtoSearchTrans.getStartDate())));//开始时间
		con.setEndDate(DateUtil.dateToString(DateUtil.stringToDate1(dtoSearchTrans.getEndDate())));//结束时间
		con.setUserRole(dtoSearchTrans.getSubCode());//科目号
		con.setTran_type_id(dtoSearchTrans.getTranType());//交易类型
		con.setCorrect(dtoSearchTrans.getTranState());//交易状态
//		int maxRows=Integer.valueOf(dtoSearchTrans.getStartPage())+Integer.valueOf(dtoSearchTrans.getEndPage());
		List<Object> paras = new ArrayList<Object>();
		String sh_cbxfmx_t_All="";
		//查所有的数据
		sh_cbxfmx_t_All=createAppNewSQL3(con,paras,0,false,false);
		if(StringUtil.isNotEmpty(sh_cbxfmx_t_All)){
			sh_cbxfmx_t_All=sh_cbxfmx_t_All.substring(6);
        }
		//分页
		String sql = "select v.* from ("+sh_cbxfmx_t_All+") v order by to_date(TRAN_DATE,'YYYY-MM-DD HH24:MI:SS') desc";
		 sql = "select v.*,rownum as numRows  from ("+sql+") v ";
	     sql = "select * from ("+sql+")";
	    // System.out.println("同步sql:"+sql);
//		sql=sql+" where numRows>="+ dtoSearchTrans.getStartPage()+" and numRows<"+maxRows;
		//sql=sql+" order by to_date(TRAN_DATE,'YYYY-MM-DD HH24:MI:SS') desc";
		//分页查询列表
		System.out.println("查询语句:"+sql);
		Query ps =this.entityManager.createNativeQuery(sql);
		if(paras.size() > 0){
			for(int i=0; i<paras.size(); i++){
				ps.setParameter(i, paras.get(i));
				//System.out.println("查询参数"+i+"值:"+paras.get(i));
				
			}
		}
		List<Object[]> ls = ps.getResultList();
		//System.out.println("查询结果1:"+ls.size());
		List<DTOResultTransApp>  rs = new ArrayList<DTOResultTransApp>();
		for(Object[] obs:ls){
			DTOResultTransApp re = new DTOResultTransApp();
			if(null!=obs[0]){
				re.setTRAN_SNO(obs[0].toString());
			}
			if(null!=obs[1]){
				String obsStr =obs[1].toString();
				double d =Double.valueOf(obsStr)*100;
				String tran_value =  this.df.format(d);
				re.setTRAN_VALUE(tran_value.replace("￥", "").replace("$", ""));	
			}
			if(null!=obs[2]){
				String obsStr =obs[2].toString();
				double d =Double.valueOf(obsStr)*100;
				String tran_amt = this.df.format(d);
				re.setTRAN_AMT(tran_amt.replace("￥", "").replace("$", ""));	
			}
			if(null!=obs[3]){
				re.setTRAN_TYPE(obs[3].toString());
			}
			if(null!=obs[4]){
				re.setTRAN_STATE(obs[4].toString());
			}
			if(null!=obs[5]){
				re.setCARD_NO(obs[5].toString());
			}
			if(null!=obs[6]){
				re.setTRAN_MRCH(obs[6].toString());
			}
			if(null!=obs[7]){
				re.setMRCH_ID(obs[7].toString());
			}
			if(null!=obs[8]){
				re.setTRAN_DATE(DateUtil.dateToString1(DateUtil.stringToDate(obs[8].toString())));
				re.setTRAN_TIME(DateUtil.dateTimeToString1(DateUtil.stringToDate(obs[8].toString())));
			}
			if(null!=obs[10]){
				re.setEQP_ID(obs[10].toString());
			}
			if(null!=obs[11]){
				re.setPAYMENT(obs[11].toString());
			}
			if(null!=obs[12]){
				re.setCARD_BALANCE(obs[12].toString());
			}
			// 新增三充字段
			if(null!=obs[16]){
				re.setORDER_STATUS(obs[16].toString());
			}
			if(null!=obs[17]){
				re.setMOBILE_PHONE(obs[17].toString());
				if(StringUtil.isNotEmpty(obs[17].toString().trim())){
					re.setIS_OP_TRAN("1"); // 属于三充交易记录
				}else{
					re.setIS_OP_TRAN("0"); // 不属于三充交易记录
				}
			}
			if(null!=obs[18]){
				re.setOP_TELECOM_TYPE(OpTranType.getOpTelecomTypeStr(obs[18].toString()));
			}
			if(null!=obs[19]){
				re.setOP_PRODUCT_ID(obs[19].toString());
			}
			if(null!=obs[20]){
				re.setOP_PRODUCT_TYPE(OpTranType.getOpProductTypeStr(obs[20].toString()));
			}
			if(null!=obs[22]){
				re.setTRAN_SUB_TYPE(obs[22].toString());
			}
			if(null!=obs[23]){
				re.setRECH_CHANNEL(obs[23].toString());
			}
			rs.add(re);
		}
		return rs;
	}
	
	/**
	 * 构造App新的查询语句(有关商充记录)
	 */
	private String createAppNewSQL3(DTOSearchCBxfmx con,List<Object> paras,int j,boolean isOld,boolean isIndustry){
		
			//构造查询语句sql
		String sh_cbxfmx_t_All="";
//		Date date=new Date();
		Date date = null;
		try {
			date = sdf.parse(con.getStartDate());
		} catch (ParseException e) {
			e.printStackTrace();
		}
		Integer month = DateUtil.getMonth2(date);
		String monthStr = month + "";
		if(month < 10) {
			monthStr = "0" + month;
		}
			String sh_cbxfmx_t=CommonSQL.getSH_CBXFMX_T_APP_NEW_SQL3(monthStr,con.getUserRole(),false,false);
			StringBuffer sb = new StringBuffer();
			if(StringUtil.isNotEmpty(con.getEmployee_id())){
				sb.append(" and v.EMPLOYEE_ID like '%"+con.getEmployee_id().trim()+"%'");
				//paras.add("%"+con.getEmployee_id().trim()+"%");
			}
			//卡号
			/*if(StringUtil.isNotEmpty(con.getCard_id())){
				sb.append(" and v.CARD_ID in (" +StringUtil.getChangeString(con.getCard_id())+")");
				//paras.add("%"+con.getCard_id().trim()+"%");
			}*/
			//交易类型
			if(StringUtil.isNotEmpty(con.getTran_type_id())){
				sb.append(" and v.TRAN_TYPE_ID in (" +StringUtil.getChangeString(con.getTran_type_id())+")");
			}
			//交易状态
			if(StringUtil.isNotEmpty(con.getCorrect())){
				sb.append(" and v.CORRECT in (" +StringUtil.getChangeString(con.getCorrect())+")");
			}
			//开始时间
			//sb.append(" and to_char(v.SETTLE_DATE,'yyyy-mm-dd') = '"+DateUtil.dateToString(date)+"'");
				//paras.add(con.getEndDate().trim());
			 sh_cbxfmx_t_All = sh_cbxfmx_t_All+" UNION select v.* from ("+sh_cbxfmx_t+") v where 1=1 "+sb.toString();
			 
			  sh_cbxfmx_t=CommonSQL.getSH_CBXFMX_T_APP_NEW_SQL3("APP",con.getUserRole(),false,false);
				StringBuffer sb1 = new StringBuffer();
				if(StringUtil.isNotEmpty(con.getEmployee_id())){
					sb1.append(" and v.EMPLOYEE_ID like '%"+con.getEmployee_id().trim()+"%'");
					//paras.add("%"+con.getEmployee_id().trim()+"%");
				}
				//卡号
				/*if(StringUtil.isNotEmpty(con.getCard_id())){
					sb1.append(" and v.CARD_ID in (" +StringUtil.getChangeString(con.getCard_id())+")");
					//paras.add("%"+con.getCard_id().trim()+"%");
				}*/
				//交易类型
				if(StringUtil.isNotEmpty(con.getTran_type_id())){
					sb1.append(" and v.TRAN_TYPE_ID in (" +StringUtil.getChangeString(con.getTran_type_id())+")");
				}
				//交易状态
				if(StringUtil.isNotEmpty(con.getCorrect())){
					sb1.append(" and v.CORRECT in (" +StringUtil.getChangeString(con.getCorrect())+")");
				}
//				sh_cbxfmx_t_All = sh_cbxfmx_t_All+" UNION select v.* from ("+sh_cbxfmx_t+") v where 1=1 "+sb1.toString();//?
		
		return sh_cbxfmx_t_All;
	}
	
	/**
	 * 构造App新的查询语句(有关商充记录)
	 */
	private String createAppNewSQL(DTOSearchCBxfmx con,List<Object> paras,int j,boolean isOld,boolean isIndustry){
		
			//构造查询语句sql
		String sh_cbxfmx_t_All="";
//		Date date=new Date();
		Date date = null;
		try {
			date = sdf.parse(con.getStartDate());
		} catch (ParseException e) {
			e.printStackTrace();
		}
		Integer month = DateUtil.getMonth2(date);
		String monthStr = month + "";
		if(month < 10) {
			monthStr = "0" + month;
		}
			String sh_cbxfmx_t=CommonSQL.getSH_CBXFMX_T_APP_NEW_SQL(monthStr,con.getUserRole(),false,false);
			StringBuffer sb = new StringBuffer();
			if(StringUtil.isNotEmpty(con.getEmployee_id())){
				sb.append(" and v.EMPLOYEE_ID like '%"+con.getEmployee_id().trim()+"%'");
				//paras.add("%"+con.getEmployee_id().trim()+"%");
			}
			//卡号
			if(StringUtil.isNotEmpty(con.getCard_id())){
				sb.append(" and v.CARD_ID in (" +StringUtil.getChangeString(con.getCard_id())+")");
				//paras.add("%"+con.getCard_id().trim()+"%");
			}
			//交易类型
			if(StringUtil.isNotEmpty(con.getTran_type_id())){
				sb.append(" and v.TRAN_TYPE_ID in (" +StringUtil.getChangeString(con.getTran_type_id())+")");
			}
			//交易状态
			if(StringUtil.isNotEmpty(con.getCorrect())){
				sb.append(" and v.CORRECT in (" +StringUtil.getChangeString(con.getCorrect())+")");
			}
			//开始时间
			sb.append(" and to_char(v.SETTLE_DATE,'yyyy-mm-dd') = '"+DateUtil.dateToString(date)+"'");
				//paras.add(con.getEndDate().trim());
			 sh_cbxfmx_t_All = sh_cbxfmx_t_All+" UNION select v.* from ("+sh_cbxfmx_t+") v where 1=1 "+sb.toString();
			 
			  sh_cbxfmx_t=CommonSQL.getSH_CBXFMX_T_APP_NEW_SQL("APP",con.getUserRole(),false,false);
				StringBuffer sb1 = new StringBuffer();
				if(StringUtil.isNotEmpty(con.getEmployee_id())){
					sb1.append(" and v.EMPLOYEE_ID like '%"+con.getEmployee_id().trim()+"%'");
					//paras.add("%"+con.getEmployee_id().trim()+"%");
				}
				//卡号
				if(StringUtil.isNotEmpty(con.getCard_id())){
					sb1.append(" and v.CARD_ID in (" +StringUtil.getChangeString(con.getCard_id())+")");
					//paras.add("%"+con.getCard_id().trim()+"%");
				}
				//交易类型
				if(StringUtil.isNotEmpty(con.getTran_type_id())){
					sb1.append(" and v.TRAN_TYPE_ID in (" +StringUtil.getChangeString(con.getTran_type_id())+")");
				}
				//交易状态
				if(StringUtil.isNotEmpty(con.getCorrect())){
					sb1.append(" and v.CORRECT in (" +StringUtil.getChangeString(con.getCorrect())+")");
				}
//				sh_cbxfmx_t_All = sh_cbxfmx_t_All+" UNION select v.* from ("+sh_cbxfmx_t+") v where 1=1 "+sb1.toString();//?
		
		return sh_cbxfmx_t_All;
	}

	@Override
	public List<DTOResultTransApp> findOldHBTranRecord() {
		String sql = "select t.EQP_ID,t.mrch_id,t.tran_sno,t.tran_value,t.TRAN_AMT,t.tran_date,t.tran_time,t.TRAN_TYPE,t.correct,t.CARD_id,t.Tran_Sub_Type "
		             +" from hnt_dev.m_tran_nor_sett_date_app t where to_date(t.tran_date,'yyyyMMdd') between to_date('20170809','yyyyMMdd') and to_date('20170809','yyyyMMdd')";
		
		return null;
	}
	
}
